package com.surfacing.rcp.db.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.surfacing.rcp.db.DataSource;
import com.surfacing.rcp.db.Queries;
import com.surfacing.rcp.db.dao.NacaDAO;
import com.surfacing.rcp.model.entity.Naca;

public class NacaDAOMySQL implements NacaDAO {

	// The data source to get the connection and the queries file
	private final DataSource source = DataSource.getInstance();
	private final Queries queries = Queries.getInstance();

	@Override
	public boolean addNaca(Naca naca) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("insert.NacaEntry"));
			query.setInt(1, naca.getNaca());
			if (query.executeUpdate() == 0)
				return false;
			// successfully
			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public List<Naca> getAllNaca() throws SQLException {
		Connection connection = source.getConnection();
		try {
			// u.username, e.primaryLocation, lo.locationname,
			// e.nacamember_ID,
			// e.firstname, e.lastname, e.sex, e.birthday, e.email,
			// *u.authorization, *u.isloggedin, *u.locked, e.city, e.street
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("list.NacaEntrys"));
			final ResultSet rs = query.executeQuery();
			// create the naca list and loop over the result
			List<Naca> nacaMembers = new ArrayList<Naca>();
			while (rs.next()) {
				Naca naca = new Naca();
				naca.setId(rs.getInt("id"));
				naca.setNaca(rs.getInt("naca"));
				nacaMembers.add(naca);
			}
			return nacaMembers;
		} finally {
			connection.close();
		}
	}

	@Override
	public Naca getNacaByID(int id) throws SQLException {
		Connection connection = source.getConnection();
		try {
			// u.username, e.primaryLocation, lo.locationname, e.Naca_ID,
			// e.firstname, e.lastname, e.sex, e.birthday, e.email,
			// *u.authorization, *u.isloggedin, *u.locked, e.city, e.street
			final PreparedStatement query = connection.prepareStatement(queries
					.getStatment("get.NacaByID"));
			query.setInt(1, (id == 0) ? 8 : id);
			final ResultSet rs = query.executeQuery();
			// assert we have a result
			if (rs.first()) {
				Naca staff = new Naca();
				staff.setId(rs.getInt("id"));
				staff.setNaca(rs.getInt("naca"));
				return staff;
			}
			// no result set
			return null;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean updateNaca(Naca naca) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query1 = connection
					.prepareStatement(queries.getStatment("update.NacaEntry"));
			query1.setInt(1, naca.getNaca());
			query1.setInt(2, naca.getId());
			// check if the update was successfully
			if (query1.executeUpdate() == 0)
				return false;

			return true;
		} finally {
			connection.close();
		}
	}

	@Override
	public boolean deleteNaca(Naca naca) throws SQLException {
		Connection connection = source.getConnection();
		try {
			final PreparedStatement query1 = connection
					.prepareStatement(queries.getStatment("delete.NacaEntry"));
			query1.setInt(1, naca.getId());
			// check if the update was successfully
			if (query1.executeUpdate() == 0)
				return false;

			return true;
		} finally {
			connection.close();
		}
	}

}
